<?php

namespace app\index\controller;

use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;

class Rcbxquery extends Common
{
	//部门查询
	public function departbxquery($page=1,$list=10){
		
		$request = Request::instance();$data = $request->post();
		$state=Db::table("ti_state")->order("paixu")->select();
		$this->assign("state",$state);
		$eid=Session::get("employee_id");
		$u=Db::table("employee")->alias("e")
		->join("department_employee de","de.employee_id=e.id")
		->join("department d","de.department_id=d.id")
		->where("e.id=$eid and de.index=1")
		->field("d.id,d.de_name")
		->find();
		$where="ap.is_deleted=0 and ap.is_actived=1 and d.id={$u["id"]} and de.index=1 ";
		if($data){
			if(trim($data["no"])){
				$no=trim($data["no"]);
				$where.="and ap.no='$no' ";
			}
			
			if(trim($data["st"])){
				$st=trim($data["st"]);
				$where.="and ap.state=$st ";
			}
			if(trim($data["title"])){
				$title=trim($data["title"]);
				$where.="and ap.title='$title' ";
			}
			if(trim($data["start"])){
				$start=strtotime(trim($data["start"]));
				$where.="and ap.ctime>=$start ";
			}
			if(trim($data["end"])){
				$end=strtotime(trim($data["end"]));
				$where.="and ap.ctime<=$end ";
			}
			if($data["deps"]){
				$where.="and d.id in ({$data["deps"]}) ";
				$data["deparr"]=Db::table("department")->where("id in ({$data["deps"]})")->select();
			}else{
				$data["deparr"]=[];
			}
			if($data["shens"]){
				$where.="and ap.cuser in ({$data["shens"]}) ";
				$data["shenarr"]=Db::table("employee")->where("id in ({$data["shens"]})")->select();
			}else{
				$data["shenarr"]=[];
			}
			if($data["shous"]){
				$where.="and ap.baoxiaoren in ({$data["shous"]}) ";
				$data["shouarr"]=Db::table("employee")->where("id in ({$data["shous"]})")->select();
			}else{
				$data["shouarr"]=[];
			}
			if($data["iszhifu"]){
				switch($data["iszhifu"]){
					case 1:$zf=1;break;
					default:$zf=0;break;
				}
				$where.="and ap.iszhifu={$zf} ";
			}

		}else{
			$data=["no"=>"","st"=>"","start"=>"","end"=>"","title"=>"","deps"=>"","deparr"=>[],"shens"=>"","shenarr"=>[],"shous"=>"","shouarr"=>[],"iszhifu"=>0];
		}
		//print_r($data);exit;
		$sum=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("ti_state s","ap.state=s.id")
		->where($where)->count();
		 $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
		$apply=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("ti_state s","ap.state=s.id")
		->where($where)->order("ap.id desc")->limit($start,$list)
		->field("ap.id,ap.no,ap.title,ce.em_name as cman,eb.em_name as bman,ap.ctime,ap.pizhunjin,ap.iszhifu,ap.zhifujin,s.title as statename,ap.state,d.de_name")
		->select();
		for($i=0;$i<count($apply);$i++){
			$apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
		}
		$this->assign("apply",$apply);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
		return $this->fetch();
			
	}

	//所有查询
	public function allbxquery($page=1,$list=10){
		
		$request = Request::instance();$data = $request->post();
		$state=Db::table("ti_state")->order("paixu")->select();
		$this->assign("state",$state);
		
		$where="ap.is_deleted=0 and ap.is_actived=1 and de.index=1 ";
		if($data){
			if(trim($data["no"])){
				$no=trim($data["no"]);
				$where.="and ap.no='$no' ";
			}
			
			if(trim($data["st"])){
				$st=trim($data["st"]);
				$where.="and ap.state=$st ";
			}
			if(trim($data["title"])){
				$title=trim($data["title"]);
				$where.="and ap.title='$title' ";
			}
			if(trim($data["start"])){
				$start=strtotime(trim($data["start"]));
				$where.="and ap.ctime>=$start ";
			}
			if(trim($data["end"])){
				$end=strtotime(trim($data["end"]));
				$where.="and ap.ctime<=$end ";
			}
			if($data["deps"]){
				$where.="and d.id in ({$data["deps"]}) ";
				$data["deparr"]=Db::table("department")->where("id in ({$data["deps"]})")->select();
			}else{
				$data["deparr"]=[];
			}
			if($data["shens"]){
				$where.="and ap.cuser in ({$data["shens"]}) ";
				$data["shenarr"]=Db::table("employee")->where("id in ({$data["shens"]})")->select();
			}else{
				$data["shenarr"]=[];
			}
			if($data["shous"]){
				$where.="and ap.baoxiaoren in ({$data["shous"]}) ";
				$data["shouarr"]=Db::table("employee")->where("id in ({$data["shous"]})")->select();
			}else{
				$data["shouarr"]=[];
			}
			if($data["iszhifu"]){
				switch($data["iszhifu"]){
					case 1:$zf=1;break;
					default:$zf=0;break;
				}
				$where.="and ap.iszhifu={$zf} ";
			}

		}else{
			$data=["no"=>"","st"=>"","start"=>"","end"=>"","title"=>"","deps"=>"","deparr"=>[],"shens"=>"","shenarr"=>[],"shous"=>"","shouarr"=>[],"iszhifu"=>0];
		}
		//print_r($data);exit;
		$sum=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("ti_state s","ap.state=s.id")
		->where($where)->count();
		 $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
		$apply=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("ti_state s","ap.state=s.id")
		->where($where)->order("ap.id desc")->limit($start,$list)
		->field("ap.id,ap.no,ap.title,ce.em_name as cman,eb.em_name as bman,ap.ctime,ap.pizhunjin,ap.iszhifu,ap.zhifujin,s.title as statename,ap.state,d.de_name")
		->select();
		for($i=0;$i<count($apply);$i++){
			$apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
		}
		$this->assign("apply",$apply);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
		return $this->fetch();
			
	}


	//部门统计
	public function departtongji($page=1,$list=10){
		
		$request = Request::instance();$data = $request->post();
		$eid=Session::get("employee_id");
		$u=Db::table("employee")->alias("e")
		->join("department_employee de","de.employee_id=e.id")
		->join("department d","de.department_id=d.id")
		->where("e.id=$eid and de.index=1")
		->field("d.id,d.de_name")
		->find();
		$did=$u["id"];
		$this->assign("did",$did);
		$where="ap.is_deleted=0 and ap.is_actived=1 and d.id={$u["id"]} and de.index=1 and ap.state=7 ";
		if($data){
			if(trim($data["no"])){
				$no=trim($data["no"]);
				$where.="and ap.no='$no' ";
			}
			
			if(trim($data["start"])){
				$start=strtotime(trim($data["start"]));
				$where.="and ap.ctime>=$start ";
			}
			if(trim($data["end"])){
				$end=strtotime(trim($data["end"]));
				$where.="and ap.ctime<=$end ";
			}
			if(trim($data["jrbm"])){
				$jrbm=trim($data["jrbm"]);
				$where.="and dfj.de_name='{$jrbm}' ";
			}
			if(trim($data["shen"])){
				$shen=trim($data["shen"]);
				$where.="and ce.em_name='{$shen}' ";
			}
			if(trim($data["kemu1"])){
				$kemu1=trim($data["kemu1"]);
				$where.="and f1.title='{$kemu1}' ";
			}
			if(trim($data["kemu2"])){
				$kemu2=trim($data["kemu2"]);
				$where.="and f1.title='{$kemu2}' ";
			}
			if(trim($data["kemu3"])){
				$kemu3=trim($data["kemu3"]);
				$where.="and f1.title='{$kemu3}' ";
			}
			if(trim($data["cn"])){
				$cn=trim($data["cn"]);
				$cn=(int)$cn;
				$where.="and (cn.title='{$cn}' or cn.title='{$cn}年') ";
			}
			if(trim($data["yue"])){
				$yue=trim($data["yue"]);
				$yue=(int)$yue;
				$where.="and (k2.title='{$yue}' or k2.title='{$yue}月') ";
			}
		}else{
			$data=["no"=>"","start"=>"","end"=>"","jrbm"=>"","shen"=>"","kemu1"=>"","kemu2"=>"","kemu3"=>"","cn"=>"","yue"=>""];
		}
		$sum=$apply=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("rc_baoxiao_detail apd","ap.id=apd.aid")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
		->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
		->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
		->join("department dfj", "ap.jrbm=dfj.id")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->where($where)->count();
		 $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

		$apply=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("rc_baoxiao_detail apd","ap.id=apd.aid")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
		->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
		->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
		->join("department dfj", "ap.jrbm=dfj.id")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->where($where)->order("ap.id desc,apd.id")->limit($start,$list)
		->field("ap.id,ap.no,ce.em_name as cman,eb.em_name as bman,ap.ctime,ap.guidangdate,apd.start,apd.end,apd.chufa,apd.mudi,cn.title as nian,k2.title as yue,f1.title as kemu1,f2.title as kemu2,f3.title as kemu3,dfj.de_name as jrdap,j.title as jxs,apd.remark,apd.pizhunfeiyong")
		->select();
		for($i=0;$i<count($apply);$i++){
			$apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
			$apply[$i]["guidangdate"]=date("Y-m-d",strtotime($apply[$i]["guidangdate"]));
		}
		$this->assign("apply",$apply);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
		return $this->fetch();
	}
	//所有统计
	public function alltongji($page=1,$list=10){
		
		$request = Request::instance();$data = $request->post();
		$eid=Session::get("employee_id");
		$u=Db::table("employee")->alias("e")
		->join("department_employee de","de.employee_id=e.id")
		->join("department d","de.department_id=d.id")
		->where("e.id=$eid and de.index=1")
		->field("d.id,d.de_name")
		->find();
		$where="ap.is_deleted=0 and ap.is_actived=1 and de.index=1 and ap.state=7 ";
		if($data){
			if(trim($data["no"])){
				$no=trim($data["no"]);
				$where.="and ap.no='$no' ";
			}
			
			if(trim($data["start"])){
				$start=strtotime(trim($data["start"]));
				$where.="and ap.ctime>=$start ";
			}
			if(trim($data["end"])){
				$end=strtotime(trim($data["end"]));
				$where.="and ap.ctime<=$end ";
			}
			if(trim($data["jrbm"])){
				$jrbm=trim($data["jrbm"]);
				$where.="and dfj.de_name='{$jrbm}' ";
			}
			if(trim($data["shen"])){
				$shen=trim($data["shen"]);
				$where.="and ce.em_name='{$shen}' ";
			}
			if(trim($data["kemu1"])){
				$kemu1=trim($data["kemu1"]);
				$where.="and f1.title='{$kemu1}' ";
			}
			if(trim($data["kemu2"])){
				$kemu2=trim($data["kemu2"]);
				$where.="and f1.title='{$kemu2}' ";
			}
			if(trim($data["kemu3"])){
				$kemu3=trim($data["kemu3"]);
				$where.="and f1.title='{$kemu3}' ";
			}
			if(trim($data["cn"])){
				$cn=trim($data["cn"]);
				$cn=(int)$cn;
				$where.="and (cn.title='{$cn}' or cn.title='{$cn}年') ";
			}
			if(trim($data["yue"])){
				$yue=trim($data["yue"]);
				$yue=(int)$yue;
				$where.="and (k2.title='{$yue}' or k2.title='{$yue}月') ";
			}
		}else{
			$data=["no"=>"","start"=>"","end"=>"","jrbm"=>"","shen"=>"","kemu1"=>"","kemu2"=>"","kemu3"=>"","cn"=>"","yue"=>""];
		}
		$sum=$apply=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("rc_baoxiao_detail apd","ap.id=apd.aid")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
		->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
		->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
		->join("department dfj", "ap.jrbm=dfj.id")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->where($where)->count();
		 $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

		$apply=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("rc_baoxiao_detail apd","ap.id=apd.aid")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
		->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
		->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
		->join("department dfj", "ap.jrbm=dfj.id")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->where($where)->order("ap.id desc,apd.id")->limit($start,$list)
		->field("ap.id,ap.no,ce.em_name as cman,eb.em_name as bman,ap.ctime,ap.guidangdate,apd.start,apd.end,apd.chufa,apd.mudi,cn.title as nian,k2.title as yue,f1.title as kemu1,f2.title as kemu2,f3.title as kemu3,dfj.de_name as jrdap,j.title as jxs,apd.remark,apd.pizhunfeiyong")
		->select();
		for($i=0;$i<count($apply);$i++){
			$apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
			$apply[$i]["guidangdate"]=date("Y-m-d",strtotime($apply[$i]["guidangdate"]));
		}
		$this->assign("apply",$apply);
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
		return $this->fetch();
	}
	//部门报销人员汇总
	public function departbxhuizong(){
		$request = Request::instance();$data = $request->post();
		$eid=Session::get("employee_id");
		$u=Db::table("employee")->alias("e")
		->join("department_employee de","de.employee_id=e.id")
		->join("department d","de.department_id=d.id")
		->where("e.id=$eid and de.index=1")
		->field("d.id,d.de_name")
		->find();
		$did=$u["id"];
		$this->assign("did",$did);
		if($data){
			if(trim($data["year"])){
				$year=(int)trim($data["year"]);
			}else{
				$year=date("Y");
			}
			if(trim($data["em_name"])){
				$em_name=trim($data["em_name"]);
			}else{
				$em_name="";
			}
			if($data["type"]){
				$type=$data["type"];
			}
		}else{
			$year=date("Y");
			$em_name="";
			$type=5;
		}

		$where="ap.is_deleted=0 and ap.is_actived=1 and d.id={$u["id"]} and de.index=1 and ap.state=7 and (cn.title='{$year}' or cn.title='{$year}年') ";
		if($em_name)
			$where.="and eb.em_name='{$em_name}'";
		$kaohe=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->where("cn.title='{$year}' or cn.title='{$year}年'")
		->order("k2.starttime")
		->field("k2.title,k2.id")
		->select();
		$kaoc=count($kaohe);
		$this->assign("kaoc",$kaoc);
		$karr=[];
		for($i=0;$i<count($kaohe);$i++){
			$karr[]=$kaohe[$i]["id"];
		}
		$kstr=implode(',',$karr);
		if($type==5){
			$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("employee ce","ap.cuser=ce.id")
			->join("department_employee de","de.employee_id=ce.id")
			->join("department d","de.department_id=d.id")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->group("eb.id")
			->where($where)
			->order("sum(ap.pizhunjin) desc")
			->field("eb.em_name,sum(ap.pizhunjin) as jine,eb.id as bman")
			->select();
		}else{
			if($type==1){
				$order="sum(ap.pizhunjin) desc";
				$list=5;
			}else if($type==2){
				$order="sum(ap.pizhunjin) desc";
				$list=20;
			}else if($type==3){
				$order="sum(ap.pizhunjin) desc";
				$list=50;
			}else{
				$order="sum(ap.pizhunjin)";
				$list=50;
			}
			$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("employee ce","ap.cuser=ce.id")
			->join("department_employee de","de.employee_id=ce.id")
			->join("department d","de.department_id=d.id")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->group("eb.id")
			->where($where)
			->order($order)->limit(0,$list)
			->field("eb.em_name,sum(ap.pizhunjin) as jine,eb.id as bman")
			->select();
		}
		

		for($i=0;$i<count($zong);$i++){
			$zong[$i]["detail"]=Db::table("rc_baoxiao_apply")->alias("ap")->join("rc_baoxiao_detail apd","ap.id=apd.aid")
							->group("ap.kaohe")
							->where("ap.kaohe in ($kstr) and ap.baoxiaoren={$zong[$i]["bman"]} and ap.is_deleted=0 and ap.is_actived=1 and ap.state=7")
							->field("ap.kaohe,sum(apd.feis) as jine")->select();

		}
		//print_r($zong);exit;
		$this->assign("kaohe",$kaohe);
		$this->assign("zong",$zong);
		$this->assign("year",$year);
		$this->assign("em_name",$em_name);
		$this->assign("type",$type);
		return $this->fetch();
	}
	//所有报销人员汇总
	public function allbxhuizong(){
		$request = Request::instance();$data = $request->post();
		
		if($data){
			if(trim($data["year"])){
				$year=(int)trim($data["year"]);
			}else{
				$year=date("Y");
			}
			if(trim($data["em_name"])){
				$em_name=trim($data["em_name"]);
			}else{
				$em_name="";
			}
			
		}else{
			$year=date("Y");
			$em_name="";
			
		}

		$where="ap.is_deleted=0 and ap.is_actived=1  and ap.state=7 and (cn.title='{$year}' or cn.title='{$year}年') ";
		if($em_name)
			$where.="and eb.em_name='{$em_name}'";
		$kaohe=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->where("cn.title='{$year}' or cn.title='{$year}年'")
		->order("k2.starttime")
		->field("k2.title,k2.id")
		->select();
		$kaoc=count($kaohe);
		$this->assign("kaoc",$kaoc);
		$karr=[];
		for($i=0;$i<count($kaohe);$i++){
			$karr[]=$kaohe[$i]["id"];
		}
		$kstr=implode(',',$karr);
		
			$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->group("eb.id")
			->where($where)
			->order("sum(ap.pizhunjin) desc")
			->field("eb.em_name,sum(ap.pizhunjin) as jine,eb.id as bman")
			->select();
		

		for($i=0;$i<count($zong);$i++){
			$zong[$i]["detail"]=Db::table("rc_baoxiao_apply")->alias("ap")->join("rc_baoxiao_detail apd","ap.id=apd.aid")
							->group("ap.kaohe")
							->where("ap.kaohe in ($kstr) and ap.baoxiaoren={$zong[$i]["bman"]} and ap.is_deleted=0 and ap.is_actived=1 and ap.state=7")
							->field("ap.kaohe,sum(apd.feis) as jine")->select();

		}
		//print_r($zong);exit;
		$this->assign("kaohe",$kaohe);
		$this->assign("zong",$zong);
		$this->assign("year",$year);
		$this->assign("em_name",$em_name);
		
		return $this->fetch();
	}
	//所有报销部门查询
	public function allbm(){
		$request = Request::instance();$data = $request->post();
		
		if($data){
			if(trim($data["year"])){
				$year=(int)trim($data["year"]);
			}else{
				$year=date("Y");
			}
			if(trim($data["em_name"])){
				$em_name=trim($data["em_name"]);
			}else{
				$em_name="";
			}
			
		}else{
			$year=date("Y");
			$em_name="";
			
		}
		$where="ap.is_deleted=0 and ap.is_actived=1  and ap.state=7 and (cn.title='{$year}' or cn.title='{$year}年') and de.index=1 ";
		if($em_name)
			$where.="and d.de_name='{$em_name}'";
		$kaohe=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->where("cn.title='{$year}' or cn.title='{$year}年'")
		->order("k2.starttime")
		->field("k2.title,k2.id")
		->select();
		$kaoc=count($kaohe);
		$this->assign("kaoc",$kaoc);
		$karr=[];
		for($i=0;$i<count($kaohe);$i++){
			$karr[]=$kaohe[$i]["id"];
		}
		$kstr=implode(',',$karr);

			$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("employee ce","ap.cuser=ce.id")
			->join("department_employee de","de.employee_id=ce.id")
			->join("department d","de.department_id=d.id")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->group("d.id")
			->where($where)
			->order("sum(ap.pizhunjin) desc")
			->field("d.de_name,sum(ap.pizhunjin) as jine,d.id as did")
			->select();
		

		for($i=0;$i<count($zong);$i++){
			$zong[$i]["detail"]=Db::table("rc_baoxiao_apply")->alias("ap")->join("rc_baoxiao_detail apd","ap.id=apd.aid")
							->join("employee ce","ap.cuser=ce.id")
							->join("department_employee de","de.employee_id=ce.id")
							->join("department d","de.department_id=d.id")
							->group("ap.kaohe")
							->where("ap.kaohe in ($kstr) and d.id={$zong[$i]["did"]} and de.index=1 and ap.is_deleted=0 and ap.is_actived=1 and ap.state=7")
							->field("ap.kaohe,sum(apd.feis) as jine")->select();

		}
		$this->assign("kaohe",$kaohe);
		$this->assign("zong",$zong);
		$this->assign("year",$year);
		$this->assign("em_name",$em_name);
		
		return $this->fetch();
	}
	//所有科目汇总
	public function allkemu(){
		$request = Request::instance();$data = $request->post();
		$cainian=Db::table("cg_cainian")->select();
		$this->assign("cainian",$cainian);
		if($data){
			if(isset($data["year"]))
				$year=Db::table("cg_cainian")->where("id={$data["year"]}")->value("title");
			else{
				$now=time();
				$data["year"]=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
				$year=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("title");
			}
			if($data["kemu2"]){
				$kemu2=Db::table("cg_feiyongkemu2")->where(["id"=>$data["kemu2"]])->value("title");
			}else{
				$kemu2="";$data["kemu2"]="";
			}
			if($data["kemu3"]){
				$kemu3=Db::table("cg_feiyongkemu3")->where(["id"=>$data["kemu3"]])->value("title");
			}else{
				$kemu3="";$data["kemu3"]="";
			}
		}else{
			$now=time();
			$data["year"]=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
			$year=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("title");
			$kemu2="";$data["kemu2"]="";$kemu3="";$data["kemu3"]="";
		}
		$where="ap.is_deleted=0 and ap.is_actived=1  and ap.state=7 and cn.id={$data["year"]} ";
		if($data["kemu2"])
			$where.="and f2.id={$data["kemu2"]} ";
		if($data["kemu3"])
			$where.="and f3.id={$data["kemu3"]} ";
		$kaohe=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->where("cn.title='{$year}' or cn.title='{$year}年'")
		->order("k2.starttime")
		->field("k2.title,k2.id")
		->select();
		$kaoc=count($kaohe);
		$this->assign("kaoc",$kaoc);
		$karr=[];
		for($i=0;$i<count($kaohe);$i++){
			$karr[]=$kaohe[$i]["id"];
		}
		$kstr=implode(',',$karr);

		$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("rc_baoxiao_detail apd","ap.id=apd.aid")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
			->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
			->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
			->group("f3.id")
			->where($where)
			->order("sum(apd.feis) desc")
			->field("f3.title as kemu3,f2.title as kemu2,f1.title as kemu1,sum(apd.feis) as jine,f3.id as kemu")
			->select();
		for($i=0;$i<count($zong);$i++){
			$zong[$i]["detail"]=Db::table("rc_baoxiao_apply")->alias("ap")->join("rc_baoxiao_detail apd","ap.id=apd.aid")
							->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
							->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
							->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
							->group("ap.kaohe")
							->where("ap.kaohe in ($kstr) and f3.id={$zong[$i]["kemu"]} and ap.is_deleted=0 and ap.is_actived=1 and ap.state=7")
							->field("ap.kaohe,sum(apd.feis) as jine")->select();

		}
		$this->assign("kaohe",$kaohe);
		$this->assign("zong",$zong);
		$this->assign("data",$data);
		$this->assign("year",$year);
		$this->assign("kemu2",$kemu2);
		$this->assign("kemu3",$kemu3);
		return $this->fetch();
			
	}
	/*查询*/
	public function depjson(){
		$where=["is_actived"=>1,"is_deleted"=>0];
		 $data = Db::table('department')->field('de_name as title,id')->where($where)->select();
//        echo Db::table('cg_feiyon"esuseway like '%\"4\"%'"gkemku3')->getLastSql();
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
	}
	 public function selectdep(){
        return $this->fetch();
    }

    //授权区域树
    public function depTree(){
        $where=["is_actived"=>1,"is_deleted"=>0];
		 $data = Db::table('department')->field('de_name as name,id')->where($where)->select();
       // print_r($data);exit;
        echo json_encode($data);
    }
    public function userjson(){
    	$where=["is_actived"=>1,"is_deleted"=>0];
		 $data = Db::table('employee')->field('em_name as title,id')->where($where)->select();
//        echo Db::table('cg_feiyon"esuseway like '%\"4\"%'"gkemku3')->getLastSql();
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
     public function selectshen(){
        return $this->fetch();
    }
     public function userTree(){
        $where=["is_actived"=>1,"is_deleted"=>0];
		  $data = Db::table('employee')->field('em_name as name,id')->where($where)->select();
       // print_r($data);exit;
        echo json_encode($data);
    }
     public function selectshou(){
        return $this->fetch();
    }
    public function kemu2json(){
    	
		 $data = Db::table('cg_feiyongkemu2')->field('title,id')->select();
//        echo Db::table('cg_feiyon"esuseway like '%\"4\"%'"gkemku3')->getLastSql();
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
    public function kemu3json(){
    	$where="esuseway like '%\"4\"%'";
		 $data = Db::table('cg_feiyongkemu3')->field('title,id')->where($where)->select();
//        echo Db::table('cg_feiyon"esuseway like '%\"4\"%'"gkemku3')->getLastSql();
        echo json_encode(['message'=>'ok','value'=>$data,'code'=>'200','redirect'=>'']);
    }
}